using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.Serialization;
using System.Data.SqlClient;

namespace Sueetie.Core
{

    public partial class DbSueetieDataProvider : SueetieDataProvider
    {

        #region ConnectionString

        private string connectionString;
        protected SqlConnection GetSqlConnection()
        {
            return new SqlConnection(ConnectionString);
        }
        public string ConnectionString
        {
            get
            {
                return connectionString;
            }
            set
            {
                connectionString = value;
            }
        }
        public DbSueetieDataProvider(string _connectionString)
        {
            this.connectionString = _connectionString;
        }

        #endregion

        #region Users

        public override SueetieUser GetUser(int userID)
        {
            SueetieUser _sueetieUser = new SueetieUser();
            using (SqlConnection cn = GetSqlConnection())
            {

                SqlCommand cmd = new SqlCommand("sueetie_user_getbyid", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = userID;
                cn.Open();

                using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        _sueetieUser.UserID = (int)dr["userid"];
                        _sueetieUser.MembershipID = DataHelper.GetGuid(dr, "membershipid");
                        _sueetieUser.UserName = dr["username"] as string;
                        _sueetieUser.Email = dr["email"] as string;
                        _sueetieUser.AvatarImage = dr["avatarimage"] as byte[];
                        _sueetieUser.AvatarImageType = dr["avatarimagetype"] as string;
                    }

                    dr.Close();
                    cn.Close();

                }
            }

            return _sueetieUser;
        }
        public override SueetieUserProfile GetSueetieUserProfile(int userID)
        {
            SueetieUserProfile _sueetieUserProfile = new SueetieUserProfile();

            using (SqlConnection cn = GetSqlConnection())
            {

                SqlCommand cmd = new SqlCommand("sueetie_User_GetProfileDetails", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@userID", SqlDbType.Int, 4).Value = userID;

                cn.Open();
                using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        _sueetieUserProfile.UserID = userID;
                        _sueetieUserProfile.UserName = dr["username"] as string;
                        _sueetieUserProfile.DisplayName = dr["displayname"] as string ?? dr["username"] as string;
                        _sueetieUserProfile.Gender = dr["gender"] as string;
                        _sueetieUserProfile.Country = dr["country"] as string;
                        _sueetieUserProfile.Occupation = dr["occupation"] as string;
                        _sueetieUserProfile.Website = dr["website"] as string;
                        _sueetieUserProfile.TwitterName = dr["twittername"] as string;
                    }

                    dr.Close();
                    cn.Close();

                }
            }
            return _sueetieUserProfile;
        }
        public override int GetUserID(string username)
        {
            int userID = -1;
            using (SqlConnection cn = GetSqlConnection())
            {
                string sql = "select userid from sueetie_users where username = '" + username + "'";

                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.CommandType = CommandType.Text;
                cn.Open();
                userID = Convert.ToInt32(cmd.ExecuteScalar().ToString());
                cn.Close();
            }

            return userID;
        }
        public override void CreateSueetieUser(SueetieUser sueetieUser)
        {
            using (SqlConnection cn = GetSqlConnection())
            {
                using (SqlCommand cmd = new SqlCommand("sueetie_User_Create", cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = sueetieUser.UserName;
                    cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 255).Value = sueetieUser.Email;
                    cmd.Parameters.Add("@MembershipID", SqlDbType.UniqueIdentifier, 16).Value = sueetieUser.MembershipID;

                    cn.Open();
                    cmd.ExecuteNonQuery();
                    cn.Close();
                }
            }

        }
        public override void UpdateSueetieUser(SueetieUser sueetieUser)
        {
            using (SqlConnection cn = GetSqlConnection())
            {
                string sql = "update sueetie_users set email = '" + sueetieUser.Email + "' where UserID = " + sueetieUser.UserID;
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.CommandType = CommandType.Text;

                cn.Open();
                cmd.ExecuteNonQuery();
                cn.Close();

            }

        }

        #endregion

        #region Avatars

        public override void UpdateSueetieUserAvatar(SueetieUserAvatar sueetieUserAvatar)
        {
            using (SqlConnection cn = GetSqlConnection())
            {
                using (SqlCommand cmd = new SqlCommand("sueetie_User_SaveAvatar", cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = sueetieUserAvatar.UserID;
                    cmd.Parameters.Add("@AvatarImage", SqlDbType.Image).Value = sueetieUserAvatar.AvatarImage;
                    cmd.Parameters.Add("@AvatarImageType", SqlDbType.NVarChar, 50).Value = sueetieUserAvatar.AvatarImageType;

                    cn.Open();
                    cmd.ExecuteNonQuery();
                    cn.Close();
                }
            }

        }
        public override void DeleteAvatar(int _userID)
        {
            using (SqlConnection cn = GetSqlConnection())
            {
                string sql = "update sueetie_useravatar set AvatarImage = null, AvatarImageType = null WHERE UserID = " + _userID;
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.CommandType = CommandType.Text;

                cn.Open();
                cmd.ExecuteNonQuery();
                cn.Close();
            }

        }

        #endregion
    }
}
